Implicit Joins

CONTENTS

Home

You can use statements such as select/from and case to access the data, performing an implicit join, as no actual join statement is used.

In this example, you add an is_tape column to the stocks table. The type is boolean. If the description contains the word TAPE it is included in your results. Your new tape_cost column in the transactions table calculates the tape cost.

implicit join bipp

Select your columns:

implicit join columns

bipp generates the SQL:

SELECT t001.invoice_num AS _0,
    case when (t000.description like '%TAPE%')
        then t001.invoice_qnty*t001.unit_price
        else 0 end AS _1
    FROM `online_retail`.`stocks` AS t000
    JOIN `online_retail`.`transactions` AS t001
    ON t000.code=t001.stock_code
    GROUP BY 1, 2

Fetch the data:

implicit join results